package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.core.util.StringUtil;
import com.tianqu.tms.dto.MarketCarGateInfoDto;

public class MarketCarGateInfoImp extends TmsDao implements MarketCarGateInfoDao {

	protected MarketCarGateInfoImp() throws TmsDaoException {
		super();
	}

	@Override
	public void insertMarketGateInfo(MarketCarGateInfoDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		final StringBuffer SQL = new StringBuffer("");
		SQL.append(" INSERT "+TmsTableNames.TMS_MARKET_CAR_GATEINFO+"( ");
		SQL.append(" SERIAL,       ");
		SQL.append(" LICENSEPLATE, ");
		SQL.append(" GATEID,       ");
		SQL.append(" GATETYPE,     ");
		SQL.append(" LOCATEDIP,    ");
		SQL.append(" CARPHOTO,     ");
		SQL.append(" THREADTYPE,   ");
		SQL.append(" THREADSTATUS, ");
		SQL.append(" CHECKFLAG,    ");
		SQL.append(" UPDATEUSERID, ");
		SQL.append(" TEMPLICENSEPLATE,");
		SQL.append(" COLOR, ");
		SQL.append(" LICENSECOLOR, ");
		SQL.append(" INSERTTIME,   ");
		SQL.append(" UPDATETIME)   ");
		SQL.append(" VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,SYSDATE(), SYSDATE() ) ");
		try {
			ps = conn.prepareStatement(SQL.toString());
			ps.setString(1, dto.getSerial());
			ps.setString(2, dto.getLicensePlate());
			ps.setInt(3, dto.getGateId());
			ps.setString(4, dto.getGateType());
			ps.setString(5, dto.getLocatedIP());
			ps.setString(6, dto.getCarPhoto());
			ps.setInt(7, dto.getThreadType());
			ps.setInt(8, dto.getThreadStatus());
			ps.setInt(9, dto.getCheckFlag());
			ps.setInt(10, dto.getUpdateUserId());
			ps.setString(11, dto.getTempLicensePlate());
			ps.setInt(12, dto.getCarColor());
			ps.setInt(13, dto.getLicenseColor());
			ps.executeUpdate();

		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());
		} finally {
			super.close(rs, ps);
		}
	}

	@Override
	public MarketCarGateInfoDto findGateInfoBySerial(String serial) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		MarketCarGateInfoDto dto = new MarketCarGateInfoDto();
		final StringBuffer SQL = new StringBuffer("");
		SQL.append(" SELECT SERIAL, ");
		SQL.append(" LICENSEPLATE, ");
		SQL.append(" GATEID, ");
		SQL.append(" GATETYPE, ");
		SQL.append(" LOCATEDIP, ");
		SQL.append(" GATETYPE, ");
		SQL.append(" CARPHOTO, ");
		SQL.append(" THREADTYPE, ");
		SQL.append(" THREADSTATUS, ");
		SQL.append(" CHECKFLAG, ");
		SQL.append(" UPDATEUSERID, ");
//		SQL.append(" INOUTMARK, ");
//		SQL.append(" CARSCORE, ");
		SQL.append(" INSERTTIME, ");
		SQL.append(" UPDATETIME ");
		SQL.append(" FROM ");
		SQL.append(TmsTableNames.TMS_MARKET_CAR_GATEINFO);
		SQL.append(" WHERE ");
		SQL.append(" SERIAL=?");
		SQL.append(" ORDER BY INSERTTIME DESC limit 1 ");
		try {
			ps = conn.prepareStatement(SQL.toString());
			ps.setString(1, serial);
			rs = ps.executeQuery();
			int i = 1;
			while (rs.next()) {
				if (i == 2) { break;}
				dto.setSerial(rs.getString("SERIAL"));
				dto.setLicensePlate(rs.getString("LICENSEPLATE"));
				dto.setGateId(rs.getInt("GATEID"));
				dto.setGateType(rs.getString("GATETYPE"));
				dto.setCarPhoto(rs.getString("CARPHOTO"));
				dto.setThreadType(rs.getInt("THREADTYPE"));
				dto.setThreadStatus(rs.getInt("THREADSTATUS"));
				dto.setCheckFlag(rs.getInt("CHECKFLAG"));
				dto.setUpdateUserId(rs.getInt("UPDATEUSERID"));
//				dto.setInoutMark(rs.getInt("INOUTMARK"));
//				dto.setCarScore(rs.getInt("CARSCORE"));
				dto.setInsertTime(rs.getTimestamp("INSERTTIME"));
				dto.setUpdateTime(rs.getTimestamp("UPDATETIME"));
				
			}
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return dto;
	}

	@Override
	// huangwei st2
	public List<MarketCarGateInfoDto> findAllGateInfo(String serial) throws TmsDaoException {
		List<MarketCarGateInfoDto> r = new ArrayList<MarketCarGateInfoDto>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		//
		if(StringUtil.isNullOrBlank(serial)) serial = "serial";
		//
		final StringBuffer SQL = new StringBuffer("");
		SQL.append(" SELECT SERIAL, ");
		SQL.append(" LICENSEPLATE, ");
		SQL.append(" GATEID, ");
		SQL.append(" GATETYPE, ");
		SQL.append(" LOCATEDIP, ");
		SQL.append(" GATETYPE, ");
		SQL.append(" CARPHOTO, ");
		SQL.append(" THREADTYPE, ");
		SQL.append(" THREADSTATUS, ");
		SQL.append(" CHECKFLAG, ");
		SQL.append(" UPDATEUSERID, ");
		SQL.append(" INSERTTIME, ");
		SQL.append(" UPDATETIME, ");
		SQL.append(" COLOR, ");
		SQL.append(" LICENSECOLOR ");
		SQL.append(" FROM ");
		SQL.append(TmsTableNames.TMS_MARKET_CAR_GATEINFO);
		SQL.append(" WHERE ");
		SQL.append(" SERIAL=? ");
		SQL.append(" ORDER BY INSERTTIME DESC");
		try {
			ps = conn.prepareStatement(SQL.toString());
			ps.setString(1, serial);
			rs = ps.executeQuery();
			while (rs.next()) {
				MarketCarGateInfoDto dto = new MarketCarGateInfoDto();
				dto.setSerial(rs.getString("SERIAL"));
				dto.setLicensePlate(rs.getString("LICENSEPLATE"));
				dto.setGateId(rs.getInt("GATEID"));
				dto.setGateType(rs.getString("GATETYPE"));
				dto.setCarPhoto(rs.getString("CARPHOTO"));
				dto.setLocatedIP(rs.getString("LOCATEDIP"));
				dto.setThreadType(rs.getInt("THREADTYPE"));
				dto.setThreadStatus(rs.getInt("THREADSTATUS"));
				dto.setCheckFlag(rs.getInt("CHECKFLAG"));
				dto.setUpdateUserId(rs.getInt("UPDATEUSERID"));
				dto.setInsertTime(rs.getTimestamp("INSERTTIME"));
				dto.setUpdateTime(rs.getTimestamp("UPDATETIME"));
				dto.setCarColor(rs.getInt("COLOR"));
				dto.setLicenseColor(rs.getInt("LICENSECOLOR"));
				r.add(dto);
			}
			return r;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}		
	}
	public List<MarketCarGateInfoDto> findGateInfoByGateId(int gateId,String day)throws TmsDaoException{
		List<MarketCarGateInfoDto> r = new ArrayList<MarketCarGateInfoDto>();
		PreparedStatement ps = null;
		ResultSet rs = null;

		final StringBuffer SQL = new StringBuffer("");
		SQL.append(" SELECT ");
		SQL.append(" SERIAL, ");
		SQL.append(" INSERTTIME, ");
		SQL.append(" LICENSEPLATE, ");
		SQL.append(" TEMPLICENSEPLATE,");
		SQL.append(" THREADSTATUS, ");
		SQL.append(" THREADTYPE, ");
		SQL.append(" CARPHOTO, ");
		SQL.append(" CHECKFLAG, ");
		SQL.append(" UPDATETIME ");
		SQL.append(" FROM ");
		SQL.append(TmsTableNames.TMS_MARKET_CAR_GATEINFO);
		SQL.append(" WHERE ");
		SQL.append(" GATEID=? ");
		SQL.append(" AND DATE_FORMAT(INSERTTIME,'%Y-%m-%d') = '"+day+"'" );
		SQL.append(" ORDER BY INSERTTIME DESC");
		try {
			ps = conn.prepareStatement(SQL.toString());
			ps.setInt(1, gateId);
			rs = ps.executeQuery();
			int i = 0;
			while (rs.next()) {
				if(i>0){
					break;
				}
				MarketCarGateInfoDto dto = new MarketCarGateInfoDto();
				dto.setSerial(rs.getString("SERIAL"));
				dto.setInsertTime(rs.getTimestamp("INSERTTIME"));
				dto.setLicensePlate(rs.getString("LICENSEPLATE"));
				dto.setTempLicensePlate(rs.getString("TEMPLICENSEPLATE"));
				dto.setThreadStatus(rs.getInt("THREADSTATUS"));
				dto.setThreadType(rs.getInt("THREADTYPE"));
				dto.setCarPhoto(rs.getString("CARPHOTO"));
				dto.setCheckFlag(rs.getInt("CHECKFLAG"));
				dto.setUpdateTime(rs.getTimestamp("UPDATETIME"));
				r.add(dto);
				i++;
			}
			return r;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}
	public List<MarketCarGateInfoDto> findGateInfoListByGateId(int gateId,String insertTime)throws TmsDaoException{
		List<MarketCarGateInfoDto> r = new ArrayList<MarketCarGateInfoDto>();
		PreparedStatement ps = null;
		ResultSet rs = null;

		final StringBuffer SQL = new StringBuffer("");
		SQL.append(" SELECT ");
		SQL.append(" SERIAL, ");
		SQL.append(" INSERTTIME, ");
		SQL.append(" LICENSEPLATE, ");
		SQL.append(" TEMPLICENSEPLATE,");
		SQL.append(" THREADSTATUS, ");
		SQL.append(" THREADTYPE, ");
		SQL.append(" CARPHOTO, ");
		SQL.append(" CHECKFLAG, ");
		SQL.append(" GATEID, ");
		SQL.append(" UPDATETIME ");
		SQL.append(" FROM ");
		SQL.append(TmsTableNames.TMS_MARKET_CAR_GATEINFO);
		SQL.append(" WHERE ");
		SQL.append(" GATEID=? ");
		SQL.append(" AND DATE_FORMAT(INSERTTIME,'%Y-%m-%d %H:%i:%s') > '"+insertTime+"'" );
		SQL.append(" ORDER BY INSERTTIME DESC");
		try {
			ps = conn.prepareStatement(SQL.toString());
			ps.setInt(1, gateId);
			rs = ps.executeQuery();
			int i = 0;
			while (rs.next()) {
				if(i>0){
					break;
				}
				MarketCarGateInfoDto dto = new MarketCarGateInfoDto();
				dto.setSerial(rs.getString("SERIAL"));
				dto.setInsertTime(rs.getTimestamp("INSERTTIME"));
				dto.setLicensePlate(rs.getString("LICENSEPLATE"));
				dto.setTempLicensePlate(rs.getString("TEMPLICENSEPLATE"));
				dto.setThreadStatus(rs.getInt("THREADSTATUS"));
				dto.setThreadType(rs.getInt("THREADTYPE"));
				dto.setCarPhoto(rs.getString("CARPHOTO"));
				dto.setCheckFlag(rs.getInt("CHECKFLAG"));
				dto.setGateId(rs.getInt("GATEID"));
				dto.setUpdateTime(rs.getTimestamp("UPDATETIME"));
				r.add(dto);
				i++;
			}
			return r;
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}
}
